package br.ufpb.sistcomp.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Class responsible for managing the operations of the database. ps: The table
 * creation script is only executed the first time that this class is stagnant,
 * if all the tables are cleared, the method should be executed runScript()
 * passing as argument the script to create all the tables.
 * 
 * @author Diego Sousa, www.diegosousa.com
 * @author Renan Oliveira, www.renanoliveira.com
 * @version 2.0
 * @since 25/01/2012
 * 
 *        Copyright (C) 2012 SistComp Empresa Jr.
 */

public class ManagerDatabase extends SQLiteOpenHelper {

	private Context context;

	/*
	 * The "super" creates the database and the getWritableDatabase() give
	 * written authorization in the database.
	 */

	public ManagerDatabase(Context context) {
		super(context, InformationDatabase.NAME_DATABASE, null,
				InformationDatabase.VERSION_DATABASE);
		this.context = context;
		getWritableDatabase();
	}

	/*
	 * The method onCreate is called only the first time that this class is
	 * stagnant.
	 */

	@Override
	public void onCreate(SQLiteDatabase sqLiteDatabase) {
		Log.i("FLAG", "Creating the database...");

		if (sqLiteDatabase != null) {
			Log.i("Flag", "Activating the option of foreign key...");
			sqLiteDatabase.execSQL("PRAGMA foreign_keys=ON;");
		}

		runScript(sqLiteDatabase, InformationDatabase.SCRIPT_CREATE_ALL_TABLE);

	}

	/*
	 * Whenever this method is called the database version is changed. Its
	 * function is to create a backup of the database tables and delete all of
	 * the current database.
	 */

	@Override
	public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion,
			int newVersion) {

		ContentValues contentValues = new ContentValues();

		SQLiteDatabase sqLiteDatabase_backup = this.context
				.openOrCreateDatabase("mycollege_backup.db",
						Context.MODE_PRIVATE, null);

		runScript(sqLiteDatabase_backup,
				InformationDatabase.SCRIPT_CREATE_ALL_TABLE);

		backupTableTeachers(sqLiteDatabase, sqLiteDatabase_backup,
				contentValues);

		backupTableSemesters(sqLiteDatabase, sqLiteDatabase_backup,
				contentValues);

		backupTableDisciplines(sqLiteDatabase, sqLiteDatabase_backup,
				contentValues);

		backupTablePoints(sqLiteDatabase, sqLiteDatabase_backup, contentValues);

		runScript(sqLiteDatabase, InformationDatabase.SCRIPT_DELETE_ALL_TABLE);

	}

	private void backupTableTeachers(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues contentValues) {

		Cursor cursor = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_TEACHERS, new String[] {
						"id_teachers", "name", "mail", "phone", "cell" }, null,
				null, null, null, null);

		if (cursor != null) {
			while (!cursor.isAfterLast()) {

				if (cursor.getPosition() == -1) {
					cursor.moveToFirst();
				}

				int columnId_teachers = cursor.getInt(0);
				String columnName = cursor.getString(1);
				String columnMail = cursor.getString(2);
				String columnPhone = cursor.getString(3);
				String columnCell = cursor.getString(4);

				contentValues.put("id_teachers", columnId_teachers);
				contentValues.put("name", columnName);
				contentValues.put("mail", columnMail);
				contentValues.put("phone", columnPhone);
				contentValues.put("cell", columnCell);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_TEACHERS, null,
						contentValues);

				cursor.moveToNext();

			}
		}
	}

	private void backupTableSemesters(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues contentValues) {

		Cursor cursor = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_SEMESTERS, new String[] {
						"id_semesters", "name" }, null, null, null, null, null);

		if (cursor != null) {
			while (!cursor.isAfterLast()) {

				if (cursor.getPosition() == -1) {
					cursor.moveToFirst();
				}

				int columnId_semesters = cursor.getInt(0);
				String columnName = cursor.getString(1);

				contentValues.put("id_semesters", columnId_semesters);
				contentValues.put("name", columnName);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_SEMESTERS, null,
						contentValues);

				cursor.moveToNext();
			}
		}
	}

	private void backupTableDisciplines(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues contentValues) {

		Cursor cursor = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_DISCIPLINES, new String[] {
						"id_discipline", "id_teacher", "id_semester", "name","media",
						"hours" }, null, null, null, null, null);

		if (cursor != null) {
			while (!cursor.isAfterLast()) {

				if (cursor.getPosition() == -1) {
					cursor.moveToFirst();
				}

				int columnId_discipline = cursor.getInt(0);
				int columnId_teacher = cursor.getInt(1);
				int columnId_semester = cursor.getInt(2);
				String columnName = cursor.getString(3);
				float columnMedia = cursor.getFloat(4);
				String columnHours = cursor.getString(5);

				contentValues.put("id_discipline", columnId_discipline);
				contentValues.put("id_teacher", columnId_teacher);
				contentValues.put("id_semester", columnId_semester);
				contentValues.put("name", columnName);
				contentValues.put("media", columnMedia);
				contentValues.put("hours", columnHours);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_DISCIPLINES, null,
						contentValues);

				cursor.moveToNext();

			}
		}
	}

	private void backupTablePoints(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues contentValues) {

		Cursor cursor = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_POINTS, new String[] {
						"id_point", "id_discipline", "values_point" }, null,
				null, null, null, null);

		if (cursor != null) {
			while (!cursor.isAfterLast()) {

				if (cursor.getPosition() == -1) {
					cursor.moveToFirst();
				}

				int columnId_point = cursor.getInt(0);
				int columnId_discipline = cursor.getInt(1);
				float columnValues_point = cursor.getFloat(2);

				contentValues.put("id_point", columnId_point);
				contentValues.put("id_discipline", columnId_discipline);
				contentValues.put("values_point", columnValues_point);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_POINTS, null,
						contentValues);

				cursor.moveToNext();
			}
		}
	}

	/*
	 * Run any script except for the select and/or any other that requires a
	 * return.
	 */
	// @Override
	// public void onDowngrade(SQLiteDatabase db, int oldVersion, int
	// newVersion){
	//
	//
	// }

	public synchronized void runScript(SQLiteDatabase sqLiteDatabase, String[] script) {

		for (String sql : script) {
			sqLiteDatabase.execSQL(sql);
		}
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);

	}

	/*
	 * closed instance SQLiteOpenHelper.
	 */

	public void closeBD() {

		close();
		// if (this.sqliteDatabase != null) {
		// this.sqliteDatabase.close();
		// }
	}
}